DROP TABLE IF EXISTS jms_ods.yl_lmdm_sys_staff;
CREATE EXTERNAL TABLE jms_ods.yl_lmdm_sys_staff(
 ID bigint comment "",  --NUMBER(11)
 NAME string comment "名称",  --NVARCHAR2(60)
 CN_NAME string comment "中文名称",  --NVARCHAR2(60)
 EN_NAME string comment "英文名称",  --NVARCHAR2(60)
 CODE string comment "编号",  --NVARCHAR2(30)
 COUNTRY_ID bigint comment "所属国家",  --NUMBER(11)
 POST_ID bigint comment "所属岗位ID",  --NUMBER(11)
 POST_NAME string comment "所属岗位名称",  --NVARCHAR2(60)
 POST_CODE string comment "岗位编码",  --NVARCHAR2(30)
 SCOPE_DISPATCH string comment "派件范围",  --NCLOB
 RECEIVING_RANGE string comment "收件范围",  --NCLOB
 CONTACT_NUMBER string comment "联系电话",  --NVARCHAR2(20)
 TOWNSHIP string comment "乡镇",  --NVARCHAR2(200)
 NETWORK_ID bigint comment "所属网点ID",  --NUMBER(11)
 NETWORK_NAME string comment "所属网点名称",  --NVARCHAR2(60)
 DEPARTMENT_ID bigint comment "部门ID",  --NUMBER(11)
 DEPARTMENT_NAME string comment "部门名称",  --NVARCHAR2(60)
 MOBILE string comment "手机号码",  --NVARCHAR2(20)
 TELEPHONE string comment "座机号",  --NVARCHAR2(20)
 ID_CARD string comment "身份证号码",  --NVARCHAR2(20)
 EXTENSION_NUMBER string comment "分机号码",  --NVARCHAR2(20)
 PHONE string comment "电话",  --NVARCHAR2(20)
 IS_REAL_NAME smallint comment "是否实名认证:1-是，2-否",  --NUMBER(4)
 IS_ENABLE smallint comment "是否启用:1启用,2不启用",  --NUMBER(4)
 IS_DELETE smallint comment "是否删除:1未删除,2已删除",  --NUMBER(4)
 CREATE_BY bigint comment "创建人ID",  --NUMBER(11)
 UPDATE_BY bigint comment "最后更新人ID",  --NUMBER(11)
 CREATE_BY_NAME string comment "创建人名称",  --NVARCHAR2(50)
 UPDATE_BY_NAME string comment "最后修改人名称",  --NVARCHAR2(50)
 CREATE_TIME timestamp comment "创建时间",  --DATE
 UPDATE_TIME timestamp comment "更新时间",  --DATE
 VERSION string comment "版本号",  --NVARCHAR2(10)
 SORT smallint comment "排序",  --NUMBER(4)
 SEX smallint comment "性别 1男 2女",  --NUMBER(4)
 EMAIL string comment "",  --NVARCHAR2(50)
 VIRTUAL_CONTRACTING_AREA_ID bigint comment "虚拟承包区ID",  --NUMBER(11)
 VIRTUAL_CONTRACTING_AREA_CODE string comment "虚拟承包区编号",  --NVARCHAR2(30)
 VIRTUAL_CONTRACTING_AREA_NAME string comment "虚拟承包区名称",  --NVARCHAR2(60)
 TYPE smallint comment "员工类别:1业务员,2操作员,3虚拟承包区,4其他",  --NUMBER(4)
 ESS_OBJECT smallint comment "电子面单对象：1业务员，2网点",  --NUMBER(4)
 CARD_TYPE smallint comment "证件类型：1身份证（默认）、2护照、3驾驶证",  --NUMBER(4)
 INCUMBENCY_STATUS tinyint comment "1.在职,2离职",  --NUMBER(1)
 IS_DELIVERY_GROUND tinyint comment "落地配标识(1:是,2:否)",  --NUMBER(1)
 BILLING_WEIGHT int comment "业务员结算重量  1-收件重量、2-入仓重量、3-订单重量、4-内部计费重量、5-集散到件重量"  --NUMBER(8)
 )
 COMMENT '员工信息'
 PARTITIONED BY (dt STRING COMMENT '更新/创建日期 (yyyy-MM-dd)')
 STORED AS PARQUET
 LOCATION '/dw/hive/jms_ods.db/external/yl_lmdm_sys_staff'
 TBLPROPERTIES (
 'discover.partitions'='false',
 'parquet.column.index.access'='true'
 );

MSCK REPAIR TABLE jms_ods.yl_lmdm_sys_staff;